﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace AWA.VAT.DbAccess.SQL
{
    public class SQLLookup
    {
        #region Editor
        public Editor GetEditorByID(int id)
        {
            using (AWA_VATEntities entity = new AWA_VATEntities())
            {
                return entity.Editors.Single(e => e.EditorID == id);
            }
        }

        public Editor GetEditorByEmail(string email)
        {
            using (AWA_VATEntities entity = new AWA_VATEntities())
            {
                return entity.Editors.Single(e => e.Email == email);
            }
        }

        public int GetEditorIDByEmail(string email)
        {
            return GetEditorByEmail(email).EditorID;
        }

        public Editor GetEditorByRealName(string realName)
        {
            using (AWA_VATEntities entity = new AWA_VATEntities())
            {
                return entity.Editors.Single(e => e.RealName == realName);
            }
        }

        public int GetEditorIDByRealName(string realName)
        {
            return GetEditorByRealName(realName).EditorID;
        }

        public Editor GetEditorByCreatorName(string creatorName)
        {
            using (AWA_VATEntities entity = new AWA_VATEntities())
            {
                return entity.Editors.Single(e => e.CreatorName == creatorName);
            }
        }

        public int GetEditorIDByCreatorName(string creatorName)
        {
            return GetEditorByCreatorName(creatorName).EditorID;
        }
        #endregion

        // Attendance

        // AMV
        public AMV GetAMVByTitle(string amvTitle)
        {
            using (AWA_VATEntities entity = new AWA_VATEntities())
            {
                return entity.AMVs.Single(a => a.AMVTitle == amvTitle);
            }
        }

        public long GetAMVIDByTitle(string amvTitle)
        {
            return GetAMVByTitle(amvTitle).AMVID;
        }
        
        // Location

        // Song

        // Artist

        // Source

        // Entry
        public Entry GetEntryByAMVID(long amvid)
        {
            using (AWA_VATEntities entity = new AWA_VATEntities())
            {
                return entity.Entries.Single(e => e.AMVID == amvid);
            }
        }

        // Award
        public Award GetAwardByName(string awardName)
        {
            using (AWA_VATEntities entity = new AWA_VATEntities())
            {
                return entity.Awards.Single(a => a.AwardName == awardName);
            }
        }

        public int GetAwardIDByName(string awardName)
        {
            return GetAwardByName(awardName).AwardID;
        }

        // Contest
        public Contest GetContestByName(string contestName)
        {
            using (AWA_VATEntities entity = new AWA_VATEntities())
            {
                return entity.Contests.Single(c => c.ContestName == contestName);
            }
        }

        public int GetContestIDByName(string contestName)
        {
            return GetContestByName(contestName).ContestID;
        }

        // Countries

        // States

        /// <summary>
        /// Returns a Dictionary of contest names to lists of winning entries
        /// </summary>
        /// <param name="year"></param>
        /// <returns></returns>
        public Dictionary<Contest, List<Entry>> GetWinners(int year)
        {
            Dictionary<Contest, List<Entry>> contestWinners = new Dictionary<Contest, List<Entry>>();
            using (AWA_VATEntities entity = new AWA_VATEntities())
            {
                var winners = entity.Entries.Where(e => e.Year == year && e.Awards.Count > 0);
                foreach (Entry e in winners)
                {
                    if (contestWinners.ContainsKey(e.Contest))
                        contestWinners.Add(e.Contest, new List<Entry>());
                    contestWinners[e.Contest].Add(e);
                }
                foreach (Contest contest in contestWinners.Keys)
                    contestWinners[contest].Sort(new EntryComparer());
            }
            return contestWinners;
        }
    }
}
